Instrument data staging and analysis

From the Staging and Analysis tab of the Instrument Data Mart page, you can copy financial instrument data to the staging table before processing of Data Quality Analysis (DQA). You can also configure DQA settings and check the quality of the customer information file (CIF) with Relationship Quality Analysis (RQA).

NOTE: The staging table is the source for processing of Funds Transfer Pricing (FTP) and for the cash flow data generated for planning.

This tab includes the following sub-tabs:

Populate Staging Table sub-tab

From this sub-tab, you can populate the staging table for DQA, and then run DQA.

TIP: You can also process DQA separately after the staging table is populated. For example, if you process DQA with a disabled setting that you intended to enable, you can rerun DQA without reloading the staging table.

The table on this sub-tab contains the following information.

Column Description
Table Lists the available instrument source tables for specific types of financial instruments, which includes certificates of deposit (CDs), credit cards, deposits, loans, and treasury assets.
Most Recent Month and Year Lists the most recent month and year for which instrument data is available for the specified type of financial instrument.
Count

Lists the number of financial instruments of the specified type in the table.

Records for Selected Month Lists the number of data records for the month listed under Most Recent Month and Year.
Instrument Staging Table Month and Year

Lists the month for which the staging table was most recently populated.

Count Lists the number of financial instruments of the specified type in the processed table.

Populate staging tables

Permission required: Administrator

  1. On the Staging and Analysis tab, click the Populate Staging Table sub-tab if it is not already selected.

  2. Under Populate Staging Table and Run DQA, select the month for which to populate tables from the Select the month to run drop-down list.

  3. Click Populate and Run DQA.

  4. In the Submitted for Processing dialog, click OK.

Run DQA on populated staging tables

Permission required: Administrator

  1. When the Axiom FI web client displays a numbered red icon indicating pending notifications,click Instrument DQA Notification in the list of notifications.

  2. On the Populate Staging Table sub-tab, click Run DQA.

  3. In the Submitted for Processing dialog, click OK.

Mark a populated tables as complete

Permission required: Administrator

  1. When the Axiom web client displays a numbered red icon indicating pending notifications,click Instrument DQA Notification in the list of notifications.

  2. On the Populate Staging Table and Run DQA page, click Mark as Complete.

  3. In the Mark as Complete dialog, click OK.

DQA Settings sub-tab

From this sub-tab, you can modify settings stored in the DQASettings table in the Axiom desktop client. These settings support optional correction of data about financial instruments.

The following table summarizes how each setting is applied in the DQA process. When either the Cash Flow Forecaster (CFF) model or the FTP model is present in your system, the data correction is applied to that model.

DQA Setting Description Valid Values Model Where Correction Applied
Amortization start date Verifies that the first amortization date occurs or after the origination, or renewal, date. If an error is detected, DQA sets the date to one payment period after the origination or start date. On, Off CFF, FTP
Amortization term auto correction setting Automatic correction of AX_AmortTerm. Default value is On. On, Off CFF, FTP
Interest index auto correction setting Automatic correction of AX_IntIndex. Default value is On. On, Off CFF only
Questionable interest rate warning threshold Warning threshold for questionable interest rates. Any AX_IntRate value that is greater than the threshold setting triggers a DQA warning message. Default value is 0.3. 0.0 to 1.0 CFF, FTP
Questionable interest spread warning threshold Warning threshold for questionable interest spreads. Any absolute AX_IntSpread value that is greater than the threshold setting triggers a DQA warning message. Default value is 0.3. 0.0 to 1.0 CFF only
Maximum number of periods archived in DQALog table Controls the maximum of historical DQA results that are stored in the DQALog table. Default value is 3. 1 to 6 CFF, FTP
Next payment date auto correction setting Automatically corrects AX_NextPmtDate. Default value is On. On, Off CFF only

Configure DQA settings: 

Permission required: Administrator

  1. On the Staging and Analysis tab, click the DQA Settings sub-tab if it is not already selected.

  2. Under View or Edit DQA Settings, click Open.

    NOTE: The DQA Settings utility opens in the Axiom desktop client.

  3. In the DQA Settings utility, edit settings in the blue fields as needed.

    TIP: The utility displays default settings when first opened. Only settings in blue fields can be edited. All other fields are read-only.

    IMPORTANT: The only configurations that can be changed in DQA are the DQA settings controlled by the DQA Settings utility. Do not attempt to modify any other component of DQA, especially Structured Query Language (SQL) logic.

  4. When you are done editing, close the utility.

DQA correction of instrument data

DQA identifies, and where possible, corrects bad loan and CD data that can cause errors in Axiom’s Cash Flow Forecaster (CFF), FTP Cash Flow, and FTP Term to Maturity models. DQA analyzes only the data points that are essential to the proper function of these models.

When instrument records contain problematic data that cannot be corrected, DQA flags those records as “failed.” Within each model, a failed instrument record is processed differently than a passing or automatically corrected record.

  • For CFF, the record is processed by the model, but the model does not project future cash flows.
  • For FTP, the models assign the default FTP rate rather than a strip-funded FTP rate or a term-to-maturity FTP rate.

DQA performs the following corrections automatically.

  • DQA sets the non-maturity flag value to 1 (TRUE) when AX_MatDate (maturity date) is null or blank. Otherwise, DQA sets the non-maturity flag value to 0 (FALSE).

    NOTE: DQA makes its own assignment of non-maturity flag values regardless of what the client provides. If the client data import uses the exact same logic as DQA, non-maturity flag values are the same after DQA as before DQA, and DQA assigns an event code of 0 (pass).

  • Axiom calculates its own maturity term as the number of months between the greater of either AX_OrigDate (origination date) or AX_RenewalDate (renewal date) when renewal date is less than the as-of date and AX_MatDate (maturity date).

RQA sub-tab

As part of the relationship import process, RPPS runs the Relationship Quality Analyzer (RQA) to identify exceptions in the imported relationships in the Customer Information File (CIF).

  • RQA also provides information on new customers and customers with invalid Tax Identification Numbers (TINs).

  • The RQA process applies whether or not you are importing relationships. RQA checks relationship and CIF quality.

After running RQA, you can save the CIF to the production tables, where it will be visible to the relationships managers. The CIF import file must be saved before the instrument detail files are imported.

During the CIF import, when you click the CIF icon on the Instrument Detail tab, you must select either Run RQA or RQA and Save to execute the import. These options are defined as follows:

  • Run RQA - This option imports the CIF file to the staging table and runs the RQA process on it. The RQA report will be available to view after this process runs. This step does not modify the CIF or TIN Exception tables. This step can be run as many times as needed saving CIF data to the production tables.

  • RQA and Save - This option does the same import and RQA process as the Run RQA option but also saves CIF data to the production tables. If relationships are imported, the new or changed relationships are also applied. Any unassigned CIFs with TINs that match a CIF in a relationship will be added to that relationship. Relationship metrics are recalculated as the last step of this process.

IMPORTANT: IMPORTANT: If you are not importing relationships, click RQA and Save. If you are importing relationships, click Run RQA.

RQA results

To view the RQA results, click View report.

RQA results provides a list of all new customers on the file and any customers with invalid TINs. If you are importing relationships, RQA also analyzes the following:

  • Relationship exceptions - Any customers on the CIF import file assigned to different relationship but with matching TINs that are not on the TIN Exception table.

  • Manager exceptions - Any groups of customers assigned to the same relationship but with multiple managers assigned to that relationship on the CIF.

  • Undefined managers - Any external manager IDs on the CIF that have not been assigned a relationship manager in the system.

  • NOTE: Before saving the CIF import, verify that all external manager IDs are assigned to a Relationship Manger. If any IDs are not assigned to a Relationship Manager, then the default Relationship Manager will be assigned to the relationship.

  • Blank Relationships - If the Import Relationship option is set to Yes, then RQA will list any customers on the CIF import file that do not have a relationship assigned. These customers remain unassigned unless their TIN matches the TIN of a customer in a relationship. In that case, the system adds them to that relationship.